

import os
import MySQLdb
import xml.etree.cElementTree as ElementTree
table_type = "posthistory"
table  = table_type+"_stackoverflow_aug13"

#source  = "D:\Research\ISEC-2014\data\content\stackoverflow\meta.serverfault.com\PostHistory.xml"
#source  = "E:\Sangeeta\dataset\\2013_05_stack_overflow\Content\stackoverflow.com\PostHistory.xml"


"""
folder_path = "D:\Research\ISEC-2014\data\content\stackoverflow"
database = "isec2014"
user = "root"
password="123"
port=3306
source  = "D:\Research\ISEC-2014\data\content\stackoverflow\meta.serverfault.com\PostHistory.xml"
"""

folder_path = "E:\Sangeeta\dataset\\2013_05_stack_overflow\Content\stackoverflow"
database = "sangeeta"
user = "sangeetal"
password="sangeetal"
port=3307
source  = "E:\Sangeeta\dataset\\2013_05_stack_overflow\Content\stackoverflow.com\PostHistory.xml"
#"""

db1= MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)
insert_cursor = db1.cursor() 


# get an iterable
context = ElementTree.iterparse(source, events=("start", "end"))

# turn it into an iterator
context = iter(context)

# get the root element
event, root = context.next()
count1 = 0
for event, entry in context:
    if event == "end" and entry.tag == "row":
        id= (str)(entry.attrib['Id'])
        print "id=",id
        #... process record elements ...
        #print "hello"
        count=0
        Comment = ""
        Text = ""
        try:
            Comment=(str)(entry.attrib['Comment'])
            Comment = Comment.replace("\n"," ")
            Comment = Comment.replace("'"," ")
            Comment = Comment.replace("\""," ")
            Comment = Comment.replace(",", " ")
            Comment = Comment.replace("(", " ")
            Comment = Comment.replace(")", " ")
            Comment = Comment.replace("\\", " ")
        except Exception ,e:
            #print "error"            
            count = count+1
            #print e, "count1=", count
        
        
        try:
            Text=(str)(entry.attrib['Text']).replace("\r"," ")
            Text = Text.replace("\n"," ")
            Text = Text.replace("'"," ")
            Text = Text.replace("\""," ")
            Text = Text.replace(",", " ")
            Text = Text.replace("("," ")
            Text = Text.replace(")", " ")
            Text = Text.replace("\\", " ")
        except Exception, e:
            #print "error"
            count = count+1
        try:
             UserId=(str)(entry.attrib['UserId'])
        except Exception, e:
             UserId = "Not given"
             count  =count+1 
        try:
              UserId_or_name=(str)(entry.attrib['UserDisplayName'])
              UserId_or_name = UserId_or_name.replace("'"," ")
        except Exception, e:
              UserId_or_name="not given"
              count = count+1
           
        insert_str = "insert into `"+ table+ "` values("+entry.attrib['Id']+","+entry.attrib['PostHistoryTypeId']+","+\
        entry.attrib['PostId']+",'"+entry.attrib['RevisionGUID']+"','"+entry.attrib['CreationDate']+"','"+\
        UserId_or_name+"','"+Comment+"','"+Text+"')"
        insert_cursor.execute(insert_str)
    root.clear()
    
db1.commit()   